package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.entity.smallClass;
import com.util.jdbcUtil;

public class smallClassDao {
	//添加
	public int add(smallClass sc){
		jdbcUtil jd=new jdbcUtil();
		String sql="insert into smallclass values(null,?,?)";
		Object obj[]={sc.getName(),sc.getBid()};
		int k=jd.update(sql, obj);
		return k;
	}
	
	//根据ID删除
	public int delById(String id){
		jdbcUtil jd=new jdbcUtil();
		String sql="delete from smallclass where id=?";
		Object obj[]={id};
		int k=jd.update(sql, obj);
		return k;
	}
	
	//根据ID修改
	public int updateById(smallClass sc){
		jdbcUtil jd=new jdbcUtil();
		String sql="update smallclass set name=?,bid=? where id=?";
		Object obj[]={sc.getName(),sc.getBid(),sc.getId()};
		int k=jd.update(sql, obj);
		return k;
	}
	
	//根据id查询
	public smallClass queryById(String id){
		jdbcUtil jd=new jdbcUtil();
		String sql="select * from smallclass where id=?";
		Object obj[]={id};
		ResultSet rs=jd.query(sql, obj);
		smallClass sc=null;
		try {
			while(rs.next()){
				sc=new smallClass(rs.getString("id"), rs.getString("name"), rs.getString("bid"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return sc;
	}
	
	//根据bid查询所有
	public List queryByBid(String bid){
		jdbcUtil jd=new jdbcUtil();
		String sql="select * from smallclass where bid=?";
		Object obj[]={bid};
		ResultSet rs=jd.query(sql, obj);
		List list=new ArrayList();
		try {
			while(rs.next()){
				smallClass sc=new smallClass(rs.getString("id"), rs.getString("name"), rs.getString("bid"));
				list.add(sc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	//根据bid查询数据总数
	public int countByBid(String bid){
		jdbcUtil jd=new jdbcUtil();
		String sql="select count(*) from smallclass where bid=?";
		Object obj[]={bid};
		ResultSet rs=jd.query(sql, obj);
		int pagecount=0;
		try {
			while(rs.next()){
				pagecount=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pagecount;
	}
	
	//根据bid查询固定数量和页码的数据
	public List queryByNumBid(int pagestart, int pagesize, String bid){
		List list=new ArrayList();
		jdbcUtil jd=new jdbcUtil();
		String sql="select * from smallclass where bid=? limit ?,?";
		Object obj[]={bid,pagestart,pagesize};
		ResultSet rs=jd.query(sql, obj);
		try {
			while(rs.next()){
				smallClass sc=new smallClass(rs.getString("id"), rs.getString("name"), rs.getString("bid"));
				list.add(sc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	//查询判断是否存在该bid
	public boolean checkBid(String bid){
		jdbcUtil jd=new jdbcUtil();
		String sql="select * from smallclass where bid=?";
		Object obj[]={bid};
		ResultSet rs=jd.query(sql, obj);
		boolean bn=false;
		try {
			bn=rs.next();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return bn;
	}
	
	//判断是否存在该名称
	public boolean checkName(String name){
		jdbcUtil jd=new jdbcUtil();
		String sql="select * from smallclass where name=?";
		Object obj[]={name};
		ResultSet rs=jd.query(sql, obj);
		boolean bn=false;
		try {
			bn=rs.next();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return bn;
	}
	
	//查询所有
	public List queryAll(){
		jdbcUtil jd=new jdbcUtil();
		String sql="select * from smallclass";
		Object obj[]={};
		ResultSet rs=jd.query(sql, obj);
		List list=new ArrayList();
		try {
			while(rs.next()){
				smallClass sc=new smallClass(rs.getString("id"), rs.getString("name"), rs.getString("bid"));
				list.add(sc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	//查询数据总数
	public int count(){
		jdbcUtil jd=new jdbcUtil();
		String sql="select count(*) from smallclass";
		Object obj[]={};
		ResultSet rs=jd.query(sql, obj);
		int pagecount=0;
		try {
			while(rs.next()){
				pagecount=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pagecount;
	}
	
	//根据页数和数据条数查询
	public List queryByNum(int pagestart, int pagesize){
		List list=new ArrayList();
		jdbcUtil jd=new jdbcUtil();
		String sql="select * from smallclass limit ?,?";
		Object obj[]={pagestart,pagesize};
		ResultSet rs=jd.query(sql, obj);
		try {
			while(rs.next()){
				smallClass sc=new smallClass(rs.getString("id"), rs.getString("name"), rs.getString("bid"));
				list.add(sc);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}
